﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataSet QueryICC_EKPOS(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1://material
                    or1.CommandText = "SELECT EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPWE1,EKPLSCOL,EKPLTERM,EKPME1,EKPMEEH,EKPPRME,EKPLSGRPBEZ"
                        + ",EKPZUAB1,EKPZUABPR1,EKPZUABWE1,EKPZUABME1,EKPABRKZ1,EKPZUAB2,EKPZUABPR2,EKPZUABWE2,EKPZUABME2,EKPABRKZ2,EKPZUAB3,EKPZUABPR3,"
                        + "EKPZUABWE3,EKPZUABME3	,EKPABRKZ3,EKPZUAB4,EKPZUABPR4,EKPZUABWE4,EKPZUABME4,EKPABRKZ4,EKPZUAB5,EKPZUABPR5,EKPZUABWE5,EKPWIEBEST,"
                        + "EKPZUABME5	,EKPABRKZ5,	EKPZUABWERT"
                         + ",  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                         + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR"
                         + ", utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE "
                        + "  FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;

                case 2://material
                    or1.CommandText = "SELECT * "
                        + "  FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;
                case 3://material
                    string EKPBEKZ = dwData["EKPBEKZ"].ToString();
                    string EKPKNTRNRL = dwData["EKPKNTRNRL"].ToString();
                    or1.CommandText = "SELECT EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ,EKPLSCOL,EKPLIEFNR,EKPWE1,EKPLTERM,EKPME1,EKPMEEH,EKPPRME,EKPLSGRPBEZ"
                        + ",EKPZUAB1,EKPZUABPR1,EKPZUABWE1,EKPZUABME1,EKPABRKZ1,EKPZUAB2,EKPZUABPR2,EKPZUABWE2,EKPZUABME2,EKPABRKZ2,EKPZUAB3,EKPZUABPR3,EKPWIEBEST,"
                        + "EKPZUABWE3,EKPZUABME3	,EKPABRKZ3,EKPZUAB4,EKPZUABPR4,EKPZUABWE4,EKPZUABME4,EKPABRKZ4,EKPZUAB5,EKPZUABPR5,EKPZUABWE5,"
                        + "EKPZUABME5	,EKPABRKZ5,	EKPZUABWERT,EKPKNTRNRL"
                         + ",  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                         + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR"
                         + ", utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE "
                        + "  FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD AND EKPBEKZ IN ( " + EKPBEKZ + ") AND trim(EKPKNTRNRL) IS " + EKPKNTRNRL + " NULL";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;
                case 4://material                  
                    or1.CommandText = "SELECT EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPWE1,EKPLSCOL,EKPLSART,EKPLSFB,EKPLTERM,EKPME1,EKPMEEH,EKPPRME,EKPLSGRPBEZ"
                        + ",EKPZUAB1,EKPZUABPR1,EKPZUABWE1,EKPZUABME1,EKPABRKZ1,EKPZUAB2,EKPZUABPR2,EKPZUABWE2,EKPZUABME2,EKPABRKZ2,EKPZUAB3,EKPZUABPR3,EKPWIEBEST,"
                        + "EKPZUABWE3,EKPZUABME3	,EKPABRKZ3,EKPZUAB4,EKPZUABPR4,EKPZUABWE4,EKPZUABME4,EKPABRKZ4,EKPZUAB5,EKPZUABPR5,EKPZUABWE5,"
                        + "EKPZUABME5	,EKPABRKZ5,	EKPZUABWERT,EKPKNTRNRL,EKPBEKZ"
                         + ",  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                         + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR"
                         + ", utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE "
                        + "  FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;
                case 6:
                    or1.CommandText = "SELECT EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPWE1,EKPLTERM,EKPLSCOL,EKPME1,EKPME3,EKPMEEH,EKPPRME,EKPLSGRPBEZ"
                        + ",EKPZUAB1,EKPZUABPR1,EKPZUABWE1,EKPZUABME1,EKPABRKZ1,EKPZUAB2,EKPZUABPR2,EKPZUABWE2,EKPZUABME2,EKPABRKZ2,EKPZUAB3,EKPZUABPR3,"
                        + "EKPZUABWE3,EKPZUABME3	,EKPABRKZ3,EKPZUAB4,EKPZUABPR4,EKPZUABWE4,EKPZUABME4,EKPABRKZ4,EKPZUAB5,EKPZUABPR5,EKPZUABWE5,EKPWIEBEST,"
                        + "EKPZUABME5	,EKPABRKZ5,	EKPZUABWERT,EKPKNTRNRL,EKPBEKZ"
                         + ",  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                         + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR"
                         + ", utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE "
                        + "  FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPME3=0 AND EKPWAKZ=:EKPWAKZ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = dwData["EKPWAKZ"].ToString();
                    break;
                case 7://material
                    or1.CommandText = "SELECT  EKPLIEFNR,LSNAME1,EKPLIEFNR,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPLSCOL,EKPWE1,EKPLTERM,EKPME1,EKPMEEH,EKPPRME,EKPLSGRPBEZ"
                        + ",EKPZUAB1,EKPZUABPR1,EKPZUABWE1,EKPZUABME1,EKPABRKZ1,EKPZUAB2,EKPZUABPR2,EKPZUABWE2,EKPZUABME2,EKPABRKZ2,EKPZUAB3,EKPZUABPR3,"
                        + "EKPZUABWE3,EKPZUABME3	,EKPABRKZ3,EKPZUAB4,EKPZUABPR4,EKPZUABWE4,EKPZUABME4,EKPABRKZ4,EKPZUAB5,EKPZUABPR5,EKPZUABWE5,EKPWIEBEST,"
                        + "EKPZUABME5	,EKPABRKZ5,	EKPZUABWERT"
                         + ",  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                         + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR"
                         + ", utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE "
                        + "  FROM EKPOS P JOIN EKKOPF K ON EKPMANDANT=EKKMANDANT AND EKPAUFKRS=EKKAUFKRS AND EKPAUFLFD=EKKAUFLFD "
                          + " LEFT JOIN LIEFDAT ON LSMANDANT=EKPMANDANT AND LSLIEFNR=EKPLIEFNR "
                        + " WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPLIEFNR=:EKPLIEFNR AND EKKBDAT >=" + dwData["EKPLDAT"].ToString()
                        + " AND EKKBDAT <=" + dwData["EKPDRUDAT"] .ToString();
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPLIEFNR", OracleType.NVarChar).Value = dwData["EKPLIEFNR"].ToString();
                    
                    break;
                case 8://material
                    or1.CommandText = "SELECT EKPLIEFNR,LSNAME1,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPLSCOL,EKPWE1,EKPLTERM,EKPME1,EKPMEEH,EKPPRME,EKPLSGRPBEZ"
                        + ",EKPZUAB1,EKPZUABPR1,EKPZUABWE1,EKPZUABME1,EKPABRKZ1,EKPZUAB2,EKPZUABPR2,EKPZUABWE2,EKPZUABME2,EKPABRKZ2,EKPZUAB3,EKPZUABPR3,"
                        + "EKPZUABWE3,EKPZUABME3	,EKPABRKZ3,EKPZUAB4,EKPZUABPR4,EKPZUABWE4,EKPZUABME4,EKPABRKZ4,EKPZUAB5,EKPZUABPR5,EKPZUABWE5,EKPWIEBEST,"
                        + "EKPZUABME5	,EKPABRKZ5,	EKPZUABWERT"
                         + ",  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                         + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR"
                         + ", utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE "
                        + "  FROM EKPOS P JOIN EKKOPF K ON EKPMANDANT=EKKMANDANT AND EKPAUFKRS=EKKAUFKRS AND EKPAUFLFD=EKKAUFLFD"
                        + " LEFT JOIN LIEFDAT ON LSMANDANT=EKPMANDANT AND LSLIEFNR=EKPLIEFNR "
                        +" WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND utl_raw.cast_to_varchar2(EKPART)=:EKPART AND EKKBDAT >=" + dwData["EKPLDAT"].ToString()
                        + " AND EKKBDAT <=" + dwData["EKPDRUDAT"].ToString();
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPART", OracleType.NVarChar).Value = dwData["EKPART"].ToString();
                    break;
                case 9://material 
                    or1.CommandText = "SELECT EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ,EKPLIEFNR,EKPLSCOL,EKPWE1,EKPLTERM,EKPME1,EKPMEEH,EKPPRME,EKPLSGRPBEZ"
                        + ",EKPZUAB1,EKPZUABPR1,EKPZUABWE1,EKPZUABME1,EKPABRKZ1,EKPZUAB2,EKPZUABPR2,EKPZUABWE2,EKPZUABME2,EKPABRKZ2,EKPZUAB3,EKPZUABPR3,EKPWIEBEST,"
                        + "EKPZUABWE3,EKPZUABME3	,EKPABRKZ3,EKPZUAB4,EKPZUABPR4,EKPZUABWE4,EKPZUABME4,EKPABRKZ4,EKPZUAB5,EKPZUABPR5,EKPZUABWE5,"
                        + "EKPZUABME5	,EKPABRKZ5,	EKPZUABWERT,EKPKNTRNRL"
                         + ",  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                         + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR"
                         + ", utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE "
                        + "  FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;
                case 31://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ,EKPLSCOL, EKPLSART,EKPLSFB,EKPWIEBEST,"
                    + "utl_raw.cast_to_varchar2(EKPART) AS EKPART,WAFBRTU,WAFBRTL,EKPEART,EKPEFARBE,EKPZUABWE1,EKPZUABWE2,EKPZUABWE3,EKPZUABWE4,EKPZUABWE5"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,"
                        + "    EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPPRME,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE FROM EKPOS P"
                        + " LEFT JOIN WASTAMM ON WAART=utl_raw.cast_to_varchar2(EKPART) "                       
                         + " WHERE EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPME3=0 "
                        + " AND EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD "
                        + " AND EKPBEKZ=:EKPBEKZ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    or1.Parameters.Add("EKPBEKZ", OracleType.NVarChar).Value = dwData["EKPBEKZ"].ToString();
                    break;
                case 32://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ,EKPLSCOL, EKPLSART,EKPLSFB,"
                    + "utl_raw.cast_to_varchar2(EKPART) AS EKPART,WAFBRTU,WAFBRTL,EKPEART,EKPEFARBE,EKPZUABWE1,EKPZUABWE2,EKPZUABWE3,EKPZUABWE4,EKPZUABWE5"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,"
                        + "    EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPPRME,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE FROM EKPOS P"
                        + " LEFT JOIN WASTAMM ON WAART=utl_raw.cast_to_varchar2(EKPART) "
                        + " WHERE  "
                           + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT "
                        + " AND EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD "; 
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;
                case 51://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ,EKPLSCOL, EKPLSART,EKPLSFB,"
                    + "utl_raw.cast_to_varchar2(EKPART) AS EKPART,WAFBRTU,WAFBRTL,EKPEART,EKPEFARBE,EKPZUABWE1,EKPZUABWE2,EKPZUABWE3,EKPZUABWE4,EKPZUABWE5"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,"
                    + "    EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPPRME,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE FROM EKPOS P "
                    + " LEFT JOIN WASTAMM ON WAART=utl_raw.cast_to_varchar2(EKPART)  "
                    + " WHERE  "
                    + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT  "
                    + " AND EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD "
                    + " AND EKPBEKZ=:EKPBEKZ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    or1.Parameters.Add("EKPBEKZ", OracleType.NVarChar).Value = dwData["EKPBEKZ"].ToString();
                    break;
                default:
                    break;
            }

            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "ICC_EKPOS";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
        public DataSet QueryICC_EKPOS(string client, string wakz, string art, string var, string color, string size, string EKPBEKZ, int option)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,EKPPRME,"
                        + "    EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE,EKPLSGRPBEZ FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ "
                        + " AND utl_raw.cast_to_varchar2(EKPART)=:EKPART AND utl_raw.cast_to_varchar2(EKPVAR)=:EKPVAR   AND utl_raw.cast_to_varchar2(EKPFARBE)=:EKPFARBE "
                        + "AND EKPGROE=:EKPGROE";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = wakz;
                    or1.Parameters.Add("EKPART", OracleType.NVarChar).Value = art;
                    or1.Parameters.Add("EKPVAR", OracleType.NVarChar).Value = var;
                    or1.Parameters.Add("EKPFARBE", OracleType.NVarChar).Value = color;
                    or1.Parameters.Add("EKPGROE", OracleType.NVarChar).Value = size;
                    break;
                case 2://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,EKPPRME,"
                        + "    EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE,EKPLSGRPBEZ FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPME1-EKPME3>0 AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ "
                        + " AND utl_raw.cast_to_varchar2(EKPART)=:EKPART AND utl_raw.cast_to_varchar2(EKPVAR)=:EKPVAR   AND utl_raw.cast_to_varchar2(EKPFARBE)=:EKPFARBE "
                        + "AND EKPGROE=:EKPGROE";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = wakz;
                    or1.Parameters.Add("EKPART", OracleType.NVarChar).Value = art;
                    or1.Parameters.Add("EKPVAR", OracleType.NVarChar).Value = var;
                    or1.Parameters.Add("EKPFARBE", OracleType.NVarChar).Value = color;
                    or1.Parameters.Add("EKPGROE", OracleType.NVarChar).Value = size;
                    break;
                case 3://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ, EKPLSART,EKPLSFB,"
                    + "utl_raw.cast_to_varchar2(EKPART) AS EKPART,WAFBRTU,WAFBRTL,EKPEART,EKPEFARBE,EKPZUABWE1,EKPZUABWE2,EKPZUABWE3,EKPZUABWE4,EKPZUABWE5"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,"
                        + "  EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPPRME,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE FROM EKPOS P  "
                        + " LEFT JOIN WASTAMM ON WAART=utl_raw.cast_to_varchar2(EKPART)  "
                         + " WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ AND EKPME3=0  "
                        //+ " AND utl_raw.cast_to_varchar2(EKPART) LIKE '" + art.Trim() + "%'  AND  utl_raw.cast_to_varchar2(EKPFARBE)='"+color+"'"
                        + " AND utl_raw.cast_to_varchar2(EKPART) LIKE '" + art.Trim() + "%'  "
                        + " AND EKPBEKZ=:EKPBEKZ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = wakz;
                    or1.Parameters.Add("EKPBEKZ", OracleType.NVarChar).Value = EKPBEKZ;
                    break;
                case 4://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,EKPPRME,EKPLSART,EKPLSFB,"
                        + "    EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE,EKPLSGRPBEZ FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ' AND EKPME1-EKPME3>0 AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ "
                        //+ " EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ "
                        + " AND utl_raw.cast_to_varchar2(EKPART)=:EKPART AND utl_raw.cast_to_varchar2(EKPVAR)=:EKPVAR   AND utl_raw.cast_to_varchar2(EKPFARBE)=:EKPFARBE "
                        + "AND EKPGROE=:EKPGROE";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = wakz;
                    or1.Parameters.Add("EKPART", OracleType.NVarChar).Value = art;
                    or1.Parameters.Add("EKPVAR", OracleType.NVarChar).Value = var;
                    or1.Parameters.Add("EKPFARBE", OracleType.NVarChar).Value = color;
                    or1.Parameters.Add("EKPGROE", OracleType.NVarChar).Value = size;
                    break;
                case 41://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,  utl_raw.cast_to_varchar2(EKPART) AS EKPART"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,EKPPRME,EKPLSART,EKPLSFB,"
                        + "    EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE,EKPLSGRPBEZ FROM EKPOS P WHERE  "
                        + "  EKPSA<>'AJ'  AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ "
                        //+ " EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ "
                        + " AND utl_raw.cast_to_varchar2(EKPART)=:EKPART AND utl_raw.cast_to_varchar2(EKPVAR)=:EKPVAR   AND utl_raw.cast_to_varchar2(EKPFARBE)=:EKPFARBE "
                        + "AND EKPGROE=:EKPGROE";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = wakz;
                    or1.Parameters.Add("EKPART", OracleType.NVarChar).Value = art;
                    or1.Parameters.Add("EKPVAR", OracleType.NVarChar).Value = var;
                    or1.Parameters.Add("EKPFARBE", OracleType.NVarChar).Value = color;
                    or1.Parameters.Add("EKPGROE", OracleType.NVarChar).Value = size;
                    break;
                case 5://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ, EKPLSART,EKPLSFB,"
                    + "utl_raw.cast_to_varchar2(EKPART) AS EKPART,WAFBRTU,WAFBRTL,EKPEART,EKPEFARBE,EKPZUABWE1,EKPZUABWE2,EKPZUABWE3,EKPZUABWE4,EKPZUABWE5"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,"
                        + "    EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPPRME,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE FROM EKPOS P "
                         + " LEFT JOIN WASTAMM ON WAART=utl_raw.cast_to_varchar2(EKPART)  "
                         + " WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ"
                        + " AND utl_raw.cast_to_varchar2(EKPART) LIKE '" + art.Trim() + "%'  "
                        + " AND EKPBEKZ=:EKPBEKZ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = wakz;
                    or1.Parameters.Add("EKPBEKZ", OracleType.NVarChar).Value = EKPBEKZ;
                    break;
                case 6://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ, EKPLSART,EKPLSFB,"
                    + "utl_raw.cast_to_varchar2(EKPART) AS EKPART,WAFBRTU,WAFBRTL,EKPEART,EKPEFARBE"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,"
                        + "  EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPPRME,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE FROM EKPOS P  "
                        + " LEFT JOIN WASTAMM ON WAART=utl_raw.cast_to_varchar2(EKPART)  "
                         + " WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ AND EKPME3=0  "
                        + " AND utl_raw.cast_to_varchar2(EKPART) LIKE '" + art.Trim() + "%'  AND  utl_raw.cast_to_varchar2(EKPFARBE)='"+color+"' and EKPGROE='"+size+"'"
                        //+ " AND utl_raw.cast_to_varchar2(EKPART) LIKE '" + art.Trim() + "%'  "
                        + " AND EKPBEKZ=:EKPBEKZ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = wakz;
                    or1.Parameters.Add("EKPBEKZ", OracleType.NVarChar).Value = EKPBEKZ;
                    break;
                case 61://material
                    or1.CommandText = "SELECT 1 AS EKPAID,EKPMANDANT,EKPAUFKRS,EKPAUFLFD,EKPAUFPOS,EKPNEUD,EKPNEUB,EKPARTGR,EKPWAKZ,EKPBEKZ, EKPLSART,EKPLSFB,"
                    + "utl_raw.cast_to_varchar2(EKPART) AS EKPART,WAFBRTU,WAFBRTL,EKPEART,EKPEFARBE"
                    + ",  utl_raw.cast_to_varchar2(EKPVAR) AS EKPVAR, utl_raw.cast_to_varchar2(EKPFARBE) AS EKPFARBE,EKPDESS,EKPCOL,EKPGROE,"
                        + "  EKPMEEH,EKPME1*0.01 AS EKPME1 ,EKPPRME,EKPLTERM,0 AS EKPQTYLOCKED,EKPME1*0.01 AS EKPQTYFREE FROM EKPOS P  "
                        + " LEFT JOIN WASTAMM ON WAART=utl_raw.cast_to_varchar2(EKPART)  "
                         + " WHERE  "
                        + "  EKPSA<>'AJ' AND EKPMANDANT=:EKPMANDANT AND EKPWAKZ=:EKPWAKZ   "
                        + " AND utl_raw.cast_to_varchar2(EKPART) LIKE '" + art.Trim() + "%'  AND  utl_raw.cast_to_varchar2(EKPFARBE)='" + color + "' and EKPGROE='" + size + "'"
                        //+ " AND utl_raw.cast_to_varchar2(EKPART) LIKE '" + art.Trim() + "%'  "
                        + " AND EKPBEKZ=:EKPBEKZ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("EKPWAKZ", OracleType.NVarChar).Value = wakz;
                    or1.Parameters.Add("EKPBEKZ", OracleType.NVarChar).Value = EKPBEKZ;
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "ICC_EKPOS";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
    }
}
